CREATE DATABASE IF NOT EXISTS lab_rezervacija;
USE lab_rezervacija;

DROP TABLE IF EXISTS neradni_dani;
DROP TABLE IF EXISTS zahtevi_registracije;
DROP TABLE IF EXISTS rezervacija;
DROP TABLE IF EXISTS sala;
DROP TABLE IF EXISTS korisnik;

CREATE TABLE korisnik (
  kid int(10) unsigned auto_increment,
  username varchar(20) NOT NULL UNIQUE,
  lozinka varchar(20) NOT NULL,
  ime varchar(20) NOT NULL,
  prezime varchar(20) NOT NULL,
  email varchar(45) NOT NULL,
  funkcija varchar(45) NOT NULL,
  isAdmin tinyint(1) NOT NULL default 0,
  PRIMARY KEY  (kid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE sala (
   sid int(10) unsigned auto_increment,
   naziv varchar(20) NOT NULL UNIQUE,
   Lokacija varchar(45) NOT NULL,
   Kapacitet int(10) unsigned NOT NULL,
   BrRacunara int(10) unsigned NOT NULL,
   ImaTablu tinyint(1) NOT NULL,
   ImaProjektor tinyint(1) NOT NULL,
  PRIMARY KEY  (SId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE rezervacija (
   rid int(10) unsigned auto_increment,
   sid int(10) unsigned,
   korisnik int(10) unsigned,
   namena varchar(80) NOT NULL,
   datum date NOT NULL,
   datumRezervisanja datetime NOT NULL,
   PocevOd time NOT NULL,
   Trajanje int(10) unsigned NOT NULL,
  PRIMARY KEY  (rid),
  FOREIGN KEY (korisnik) REFERENCES korisnik (kid) ON UPDATE CASCADE on DELETE CASCADE,
  FOREIGN KEY (sid) REFERENCES sala (sid) ON UPDATE CASCADE on DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



CREATE TABLE zahtevi_registracije (
  zid int(10) unsigned auto_increment,
  username varchar(20) NOT NULL UNIQUE,
  lozinka varchar(20) NOT NULL,
  ime varchar(20) NOT NULL,
  prezime varchar(20) NOT NULL,
  email varchar(45) NOT NULL,
  funkcija varchar(45) NOT NULL,
  vreme_zahteva datetime NOT NULL,
  vreme_odluke datetime,
  status int NOT NULL default 0,
  PRIMARY KEY (zid)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE neradni_dani (
  ndid int(10) unsigned auto_increment,
  sid int(10) unsigned not null,
  datum date NOT NULL,
  vreme_evidentiranja datetime NOT NULL,
  korisnik int(10) unsigned,
  razlog varchar(45),
  PRIMARY KEY (ndid),
 FOREIGN KEY (sid) REFERENCES sala (sid) ON UPDATE CASCADE on DELETE CASCADE,
 FOREIGN KEY (korisnik) REFERENCES korisnik (kid) ON UPDATE CASCADE on DELETE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO sala (naziv,lokacija,kapacitet,BrRacunara,ImaTablu,ImaProjektor) VALUES
 ('laboratorija 25','paviljon Rašović',30,30,1,1),
 ('laboratorija 26','paviljon Rašović',40,40,0,1),
 ('laboratorija 60','prizemlje',30,30,1,1),
 ('učionica 61','prizemlje',50,0,1,1);



INSERT INTO korisnik (username, lozinka, ime, prezime, email, funkcija) VALUES
 ('bosko','bole','Boško','Nikolić','nbosko@etf.rs','docent'),
 ('djolent','djole','Đorđe','Jevđic','djordje.jevdjic@etf.rs','asistent'),
 ('joca','joca','Jovan','Đorđević','jdjordjevic@etf.rs','redovni profesor'),
 ('zaharije','zaki','Zaharije','Radivojević','zaki@etf.rs','asistent'),
 ('andrija','pirocanac','Andrija','Bošnjaković','andrija@etf.rs','asistent'),
 ('gliga','milos','Miloš','Gligorić','gliga@etf.rs','asistent'),
 ('jeca','jeca007','Jelica','Protić','jeca@sezampro.yu','redovni profesor'),
 ('dmilicev','dragan','Dragan','Milićev','dmilicev@etf.rs','docent'),
 ('igor','iggy','Igor','Tartalja','tartalja@afrodita.bg.ac.yu','vanredni profesor'),
 ('laslo','laksi','Laslo','Kraus','kraus@etf.rs','asistent'),
 ('mvt','milo008','Milo','Tomašević','mvt@etf.rs','vanredni profesor');
 
 
INSERT INTO korisnik (username, lozinka, ime, prezime, email, funkcija, isAdmin) VALUES  
 ('nenadko','nenad111','Nenad','Korolija','nenadko@etf.rs','administrator',1),
 ('mira','shefica','Mirjana','Cvetković','mirac@etf.rs','administrator',1),
 ('dragan','draganche','Dragan','Miladinovic','dragan.miladinovic@gmail.com','administrator',1);

 



INSERT INTO rezervacija (sid, korisnik, Namena,Datum,PocevOd,Trajanje,datumRezervisanja) VALUES
 (1,2,'ispit PIA','2009-11-27','14:00:00',4,'2008-12-10 12:00:00'),
 (2,2,'ispit PIA','2009-11-27','14:00:00',4,'2008-12-10 12:00:00'),
 (1,2,'odbrana domaćih iz PRS-a','2009-11-28','12:00:00',6,'2008-12-10 14:00:00'),
 (1,2,'odbrana domaćih iz PRS-a','2009-11-30','14:00:00',4,'2008-12-10 15:00:00'),
 (1,1,'ispit PIA','2009-11-15','17:00:00',3,'2008-12-10 16:00:00'),
 (2,1,'ispit PIA','2009-11-15','17:00:00',3,'2008-12-10 16:00:00'),
 (1,6,'odbrana domaćih iz Eksperskih sistema','2009-11-19','13:00:00',6,'2008-12-10 18:00:00'),
 (2,6,'odbrana domaćih iz Eksperskih sistema','2009-11-19','13:00:00',6,'2008-12-10 18:00:00'),
 (1,6,'odbrana domaćih iz Eksperskih sistema','2009-11-20','14:00:00',6,'2008-12-10 19:00:00'),
 (2,6,'odbrana domaćih iz Eksperskih sistema','2009-11-20','14:00:00',6,'2008-12-10 19:00:00'),
 
 (1,2,'lab. vezbe iz AOR2','2009-11-17','12:00:00',4,'2008-12-10 20:00:00'),
 (2,2,'lab. vezbe iz AOR2','2009-11-17','12:00:00',4,'2008-12-10 20:00:00'),
 (1,2,'lab. vezbe iz AOR2','2009-11-19','08:00:00',4,'2008-12-10 20:00:00'),
 (2,2,'lab. vezbe iz AOR2','2009-11-19','08:00:00',4,'2008-12-10 20:00:00'),
 (1,2,'lab. vezbe iz AOR2','2009-11-20','10:00:00',4,'2008-12-10 20:00:00'),
 (2,2,'lab. vezbe iz AOR2','2009-11-20','10:00:00',4,'2008-12-10 20:00:00');


-- status 0 znaci da zahtev jos nije razrese
-- status 1 znaci da je zahtev odobren
-- status 2 znaci da je zahtev odbijen

INSERT INTO zahtevi_registracije (username, lozinka, ime, prezime, email, funkcija,vreme_zahteva) VALUES 
('cmilos', 'cmiki', 'Miloš', 'Cvetanović', 'cmilos@etf.rs', 'asistent','2009-04-19 16:12:00'),
('bfurlan', 'fuki', 'Bojan', 'Furlan', 'bfurlan@etf.rs', 'asistent','2009-04-20 16:12:00'),
('mare_kralj', 'mare', 'Marinko', 'Rokvić', 'mare_kralj@yahoo.com', 'blejač','2009-03-21 18:15:23'),
('tasa', 'panta', 'Tasić', 'Nebojša', 'nebojsa.t@gmail.com', 'student','2009-05-06 17:12:00'),
('sunchica','mala_plava','Sunčica','Anđelković','asuncica@etf.rs','administrator','2009-05-14 11:19:00');


INSERT INTO zahtevi_registracije (username, lozinka, ime, prezime, email, funkcija,vreme_zahteva, vreme_odluke, status) VALUES 
('slatki','greh','Lepa','Brena','brena@hotmail.com','pevaljka','2008-12-14 11:19:00','2008-12-15 14:10:11',2),
('gliga','milos','Miloš','Gligorić','gliga@etf.rs','asistent','2008-02-11 11:19:00','2008-02-13 11:13:23',1),
('mira','shefica','Mirjana','Cvetković','mirac@etf.rs','administrator','2006-07-11 10:50:00','2006-07-12 12:15:23',1);

INSERT INTO neradni_dani (sid, datum, vreme_evidentiranja, korisnik, razlog) VALUES 
(1, '2009-11-29', '2009-05-14 11:28:00', 12, '29. novembar!!!'),
(2, '2009-11-29', '2009-05-14 11:28:00', 12, '29. novembar!!!'),
(1, '2009-12-31', '2009-05-14 11:29:00', 13, 'Nova godina!!!'),
(2, '2009-12-31', '2009-05-14 11:29:00', 13, 'Nova godina!!!');

